<?php

require_once PATH_FFWD.'core/fieldvalidators.php';

class baseTable{
	static $database=NULL;
	static $fields;
	public $flds;
	public $tablename='';
	public $dbase='';
	protected $pk=NULL;
	public function __construct(){
		if(!self::$database)self::$database=$GLOBALS['db']->database; 
		$this->tablename=substr(strtolower(get_class($this)),0,-5);
		eval('$this->dbase='.$this->tablename.'Table::$database;');
		$this->flds=$this->getFields();
		foreach($this->flds as $k=>$v){
			if(isset($v['key'])){
				$this->pk=$k;
				break;
			}
		}
	}
	
	public function formatRow(&$row){
		foreach($row as $k=>$v){
			if(!isset($this->flds[$k]))continue;
			switch($this->flds[$k]['type']){
				case 'boolean':{
					if($v)$row[$k]='&#10004;';else $row[$k]=' ';
					break;
				}	
				case 'date':{
					if(!$v || $v=='0000-00-00')$row[$k]='    -  -  ';
					break;					
				}		
			}
		}
	}
	
	public function query_join($p){
		$fields=&$this->getFields();
		$q='SELECT ';		
		if(isset($p['cols'])){
			foreach($fields as $k=>$v)if(in_array($k,$p['cols']))$q.=''.$v['query'].', ';
		}else{
			foreach($fields as $k=>$v)$q.=''.$this->getQuery($k).", \n";
		}
		$q=substr($q,0,-3).' FROM `'.$this->tablename.'` ';
		$joins=Array();
		foreach($fields as $k=>$v){
			if(isset($v['join'])){
				if(in_array($v['query'],$joins))continue;
				$joins[]=$v['query'];				
				$q.="\n LEFT JOIN ";
				if(isset($v['join']['database']))$q.='`'.$v['join']['database'].'`.';
				$q.='`'.$v['join']['table'].'` ON '."\n";
				$q.='`'.$this->tablename.'`.`'.$v['join']['local'].'` = ';
				if(isset($v['join']['database']))$q.='`'.$v['join']['database'].'`.';
				$q.='`'.$v['join']['table'].'`.`'.$v['join']['column'].'`';
			}
		};
		$q.=' WHERE 1 ';
		if(isset($p['condition']))$q.=" AND ".$p['condition'].' ';
		if(isset($p['limit']))$q.=' LIMIT '.$p['limit'].' ';
		return $GLOBALS[$this->dbase]->query($q);			
	}
	
	
	public function query($p){
		$fields=$this->getFields();
		$q='SELECT ';		
		if(isset($p['cols'])){
			foreach($fields as $k=>$v)
				if(in_array($k,$p['cols']))$q.=''.$v['query'].', ';
		}else{
			foreach($fields as $k=>$v){				
				if(isset($v['foreign']))continue;
				$q.=''.$this->getQuery($k).", \n";
			}
		}
		$q=substr($q,0,-3).' FROM `'.$this->tablename.'` ';
		$q.=' WHERE 1 ';
		if(isset($p['condition']))$q.=" AND ".$p['condition'].' ';
		if(isset($p['limit']))$q.=' LIMIT '.$p['limit'].' ';
		return $GLOBALS[$this->dbase]->query($q);			
	}
	
	
	public function __call($name,$arguments){
		if(strpos($name,'getBy')===0){
			if(empty($arguments))return 0;
			$fields=$this->getFields();
			$field=strtolower(substr($name,5));
			if($field=='pk')$field=$this->pk;
			$clsn=substr(get_class($this),0,-5).'Record';
			$e=new $clsn(0);			
			if($field=='id' && $arguments[0] && $arguments[0]=='new'){
				$e->id='new';
				return $e;
			};
			$fields=$this->getFields();
			$found=0;
			foreach($fields as $k=>$v){
				if($field==strtolower($k)){
					$found=1;
					$field=$k;
					break;
				}
			};
			
			if(!$found)throw new Exception('no_such_field:'.$field);
			if(!$this->dbase)throw new Exception('DBASE property undefined');
			if(!isset($fields[$field]['join'])){
				$fieldtable=$this->tablename;
			}else{
				$fieldtable=$fields[$field]['join']['table'];
			};
			if(isset($fields[$field]['nq']))$nq=$fields[$field]['nq'];else $nq=0;
			$a=Array(
				'condition'=>
					Array(
						''.$fieldtable.'.'.$field.''=>
						($nq?'"':'').$this->getDB()->escape_string($arguments[0]).($nq?'"':'')
					)
			);
			$q=$this->buildQuery(array_keys($fields),$a['condition'])." LIMIT 1 ";
			console_log($q);
			$s=$this->getDB()->query($q);
			if(!$s)throw new Exception("BaseTable::__call($name,$arguments) -> sql_error:".$this->getDB()->lastError());
			if(!$s->num_rows)return NULL;
			$e->fromRow($s->fetch_assoc());			
			return $e;						
		};
		if(strpos($name,'has')===0){
			if(empty($arguments))return 0;
			$field=strtolower(substr($name,3));
			$fields=$this->getFields();
			$fld=0;
			foreach($fields as $k=>$v){
				if(strtolower($k)==$field){
					$fld=$v;
					break;
				}
			}
			if(!$fld)return 0;
			$db=$this->getDB();
			$q="SELECT * FROM `".$this->tablename."` WHERE ".$v['query']." LIKE '".$arguments[0]."' ";
			$rs=$db->query($q);
			return $rs->num_rows;
		}
	}
	
	static function &getFields(){//must be implemented on every derived class (in PHP5.2)
		return self::$fields;
	}
	
	public function describe(){
		echo '<pre>';
		print_r($this->getFields());
		echo '</pre>';
	}
	
	public function __toString(){
		return $this->tablename;
	}
	
	public function getquery($f){
		$flds=$this->getFields();
		if(!isset($flds[$f])){throw new Exception('field_doesnt_exist:'.$f);return "'NULL'";};
		if(!isset($flds[$f]['join'])){
			$q=$flds[$f]['query'];
		}else{
			$q='(SELECT '.$flds[$f]['query'].' FROM ';
			if(isset($flds[$f]['join']['database']))$q.='`'.$flds[$f]['join']['database'].'`.';
			$q.='`'.$flds[$f]['join']['table'].'` WHERE ';
			if(isset($flds[$f]['join']['database']))$q.='`'.$flds[$f]['join']['database'].'`.';
			$q.='`'.$flds[$f]['join']['table'].'`.`'.$flds[$f]['join']['column'].'` = `'.$this->tablename.'`.`'.$flds[$f]['join']['local'].'` LIMIT 1 )';
		};
		$q.=" AS `$f` ";
		return $q;
	}
	
	public function buildQuery($cols,$condition=Array(),$updatevalues=NULL){
		if($updatevalues){
		}else{
			$query="SELECT ";$c=65;$aliases=Array();
		};
		
		if(DEBUG){
			console_log('cols:'.implode(',',$cols));		
			console_log('conditions'.implode(';',$condition));
		};
		foreach($cols as $f){
			if(!isset($this->flds[$f]))continue;
			$fq=$this->flds[$f]['query'];
			if($fq[0]!='('){
				if(!preg_match('/([a-zA-Z0-9_`]*\.[a-zA-Z0-9_`]*)\.([a-zA-Z0-9_`]*)/',$fq,$fqa)){
					preg_match('/([a-zA-Z0-9_`]*)\.([a-zA-Z0-9_`]*)/',$fq,$fqa);
				}
				array_shift($fqa);
				if(isset($this->flds[$f]['join'])){
					if(!isset($aliases[ implode('|',$this->flds[$f]['join']) ])){
						$aliases[ implode('|',$this->flds[$f]['join']) ]=Array(	'alias'=>'A'.chr($c++),
																																		'join'=>&$this->flds[$f]['join'],
																																		'table'=>$fqa[0],
																																		'query'=>$fqa[1]);
					};
					$tablealias=&$aliases[implode('|',$this->flds[$f]['join'])]['alias'];
					$query.=str_replace($fqa[0],$tablealias,$fq).' as \''.$f.'\', ';
				}else{			
					$query.=" A0.`".$f."`, ";
				};	
			}else{
				$query.=$fq." as '$f', ";
			}		
		};
		$query=substr($query,0,-2)." FROM `".$this->tablename."` A0 ";
		/**/
		//if(DEBUG)print_var($aliases);
		foreach($aliases as $alias){
			$query.=" LEFT JOIN ".$alias['table'].' '.$alias['alias']." ON ".
							' ( ';
			$ta="A0";
			$tj=$alias['join']['local'];
			$ls=explode('.',$alias['join']['local']);
			if(count($ls)>1){
				$tj=$ls[1];
				$ta=$ls[0];
				foreach($aliases as $alik){
					if($alik['join']['table']==$ta){
						$ta=$alik['alias'];
						break;
					}
				}
			};
			
			$query.=' '.$ta.'.'.$tj.' = '.$alias['alias'].'.'.$alias['join']['column'];
			if(isset($alias['join']['condition'])){
				$query.=" AND ";
				$query.=str_replace($alias['table'],$alias['alias'],$alias['join']['condition']);
			}							
			$query.=' ) ';
		};		
		$query.=" WHERE 1 ";
		foreach($condition as $k=>$v){
			if(substr($k,0,strlen($this->tablename))==$this->tablename)//regexp talĂ„â€šĂ‹â€ˇn?
				$k=str_replace($this->tablename,'A0',$k);
			else{
				/**/
				$o=explode('.',$k);
				if(count($o)>1){
					$tt=$o[0];
					$tf=$o[1];
					foreach($aliases as $ak=>$av){
						if($av['join']['table']==$tt){
							$tt=$av['alias'];
							$k=$tt.".".$tf;
							break;
						}
					};
				};		
			};
			if($v=='NULL')
				$query.=" AND $k IS NULL";
			else{
				$v=$GLOBALS['db']->escape_string($v);
				if(isset($this->flds[$k])){
					if(isset($this->flds[$k]['nq']) && $this->flds[$k]['nq']){
						$v="'$v'";
					}
				}else
				if(!is_numeric($v) && !is_bool($v))$v="'$v'";
				$query.=" AND $k = $v";
			};
		};
		return $query; 
	}
	
	public function &getDB(){
		if(!$this->dbase)return 0;		
		if(!isset($GLOBALS[$this->dbase])){
			throw(new Exception('Connection variable does not exist: $'.$this->dbase));
		}
		return $GLOBALS[$this->dbase];	
	}
	
	public function rebuild(){
		$this->getDB()->buildTable($this->tablename);
	}
	
	public function getPrimaryKey(){
		return $this->pk;
	}
	
	public function getBy($cond){
		global $db;
		$condition='';
		$db=$this->getDB();
		$fields=$this->getFields();
		$condition=Array();
		foreach($cond as $field=>$value){
			if(!isset($fields[$field]))throw new Exception('Field does not exist in table: '.$field);
			$condition[$field]=$db->escape_string($value);
		};
		$cols=$this->getFields();
		$q=$this->buildQuery(array_keys($this->getFields()),$condition);
		$rs=$this->getDB()->query($q);
		if(!$rs->num_rows)return 0;
		$e=$this->getById('new');
		$e->fromRow($rs->fetch_assoc());			
		return $e;						
	}
	
	//============================ everything as static ============================
	
	public static function __callStatic($name,$arguments){
		//phpinfo();
		$o=get_called_class();
		$o=new $o;
		if(strpos($name,'getBy')===0){
			if(empty($arguments))return 0;
			$fields=$o->getFields();
			$field=strtolower(substr($name,5));
			if($field=='pk')$field=$o->pk;
			$clsn=substr(get_class($o),0,-5).'Record';
			$e=new $clsn(0);			
			if($field=='id' && $arguments[0] && $arguments[0]=='new'){
				$e->id='new';
				return $e;
			};
			$fields=$o->getFields();
			$found=0;
			foreach($fields as $k=>$v){
				if($field==strtolower($k)){
					$found=1;
					$field=$k;
					break;
				}
			};
			if(!$found)throw new Exception('no_such_field:'.$field);
			if(!$o->dbase)throw new Exception('DBASE property undefined');
			$a=Array(
				'condition'=>
					Array(
						''.$o->tablename.'.'.$field.''=>$arguments[0]
					)
			);
			$q=$o->buildQuery(array_keys($fields),$a['condition'])." LIMIT 1 ";
			console_log($q);
			$s=$o->getDB()->query($q);
			if(!$s)throw new Exception("BaseTable::__call($name,$arguments) -> sql_error:".$o->getDB()->lastError());
			if(!$s->num_rows){
				console_log('record not found');
				return NULL;
			}
			$e->fromRow($s->fetch_assoc());			
			return $e;						
		};
		if(strpos($name,'has')===0){
			if(empty($arguments))return 0;
			$field=strtolower(substr($name,3));
			$fields=$this->getFields();
			$fld=0;
			foreach($fields as $k=>$v){
				if(strtolower($k)==$field){
					$fld=$v;
					break;
				}
			}
			if(!$fld)return 0;
			$db=$this->getDB();
			$q="SELECT * FROM `".$this->tablename."` WHERE ".$v['query']." LIKE '".$arguments[0]."' ";
			$rs=$db->query($q);
			return $rs->num_rows;
		}
	}
	
	public function getAll($cond=Array()){
		if(empty($cond))console_warn('Dangerous to call BaseTable::getAll() without condition!');
		$fields=$this->getFields();
		$q=$this->buildQuery(array_keys($fields),$cond);
		$result=Array();
		$rs=$this->getDB()->query($q);
		$clsn=substr(get_class($this),0,-5).'Record';
		while($row=$rs->fetch_assoc()){
			$result[$row[$this->pk]]=new $clsn(0);
			$result[$row[$this->pk]]->fromRow($row); 
		}
		return $result;		
	}
	
	public function buildSQL(){
		$flds=$this->getFields();
		//print_var($flds);
		$q='CREATE TABLE IF NOT EXISTS `'.$this."` (\n";
		$nosizetypes=Array('boolean');
		foreach($flds as $k=>$v){
			if(isset($v['join']))continue;
			$nq=((isset($v['nq']) && $v['nq'])?'"':'');			
			$q.="\t`".$k."` ".$v['type'].
					((!in_array($v['type'],$nosizetypes) && isset($v['size']) && $v['size'])?('('.$v['size'].') '.
						(($v['type']=='int' && $v['size']==10)?' UNSIGNED ':'')
						):' ').
					((isset($v['NULL']))?'':' NOT NULL ').
					((isset($v['key']) && $v['key'])?' auto_increment ':'').
					((isset($v['default']))?' DEFAULT '.$nq.$v['default'].$nq.' ':(isset($v['NULL'])?' default NULL ':'') ).
					",\n";
		};
		$pk=$this->getPrimaryKey();			
		if($pk){
			$q.="\tPRIMARY KEY (`".$pk."`),\n";
		};
		$q=substr($q,0,-2)."\n) ";
		$q.=" ENGINE=MyISAM  DEFAULT CHARSET=utf8 ";
			
		
		console_log($q,$this.'Table::buildSQL()');
		$this->getDB()->query($q);
	}
	
};
